package session03.orm;

import session02.orm.DBUtil;
import session03.bean.PlayerBean;
import session03.bean.TeamBean;

import java.sql.*;
import java.util.ArrayList;

public class TestMain {
    public static void main(String[] args) {
        //查询出所有来自“尤文图斯”俱乐部的球员
        TeamBean team = getPlayerByclub("尤文图斯");
        for (PlayerBean player : team.getMyPlayer()) {
            System.out.println(player);
        }

/*        //查询出名字后缀有“斯基”或“维奇”的球员，要求也显示他们的所属国家队 （球员当中要有国家）
        ArrayList<TeamBean> teamLst = getCountryWithPlayer("斯基", "维奇");
        for (TeamBean t : teamLst) {
            System.out.println(t);
            for (PlayerBean p : t.getMyPlayer()) {
                System.out.println(p);
            }
            System.out.println("======================");
        }*/

/*        //查询出在”巴黎“比赛的有哪些门将，要求也要显示他们所属的国家队
        ArrayList<TeamBean> teamLst = getGoalkeeperWithCountry("巴黎", "门将");
        for (TeamBean t : teamLst) {
            System.out.println(t);
            for (PlayerBean p : t.getMyPlayer()) {
                System.out.println(p);
            }
            System.out.println("======================");
        }*/

/*        //查询出“A组”各队带了哪些前锋
        ArrayList<TeamBean> teamLst = getForwardWithPlayer("A组", "前锋");
        for (TeamBean t : teamLst) {
            System.out.println(t);
            for (PlayerBean p : t.getMyPlayer()) {
                System.out.println(p);
            }
            System.out.println("======================");
        }*/

/*        //在”H组“中增加”中国队“，同时让该小组的球队数目+1
        insert("F组", "中国队");*/
    }

    //查询出所有来自“尤文图斯”俱乐部的球员
    public static TeamBean getPlayerByclub(String club) {
        TeamBean team = null;
        String sql = "select pk_playerid,f_dateOfBirth,f_club,f_name from t_player where f_club = ?";

        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, club);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                if (team == null) {
                    team = new TeamBean();
                }

                PlayerBean p = new PlayerBean();
                p.setPlayerID(rs.getInt("pk_playerid"));
                p.setDate(rs.getDate("f_dateOfBirth"));
                p.setClub(rs.getString("f_club"));
                p.setName(rs.getString("f_name"));

                team.getMyPlayer().add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return team;
    }

    //查询出名字后缀有“斯基”或“维奇”的球员，要求也显示他们的所属国家队 （球员当中要有国家）
    public static ArrayList<TeamBean> getCountryWithPlayer(String s1, String s2) {
        ArrayList<TeamBean> teamLst = new ArrayList<>();
        String sql = "select pk_teamid,f_teamName,pk_playerid,f_name from t_team,t_player where pk_teamid=fk_teamid " +
                "and (f_name like ? or f_name like ?) order by pk_teamid";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, "%" + s1);
            ps.setString(2, "%" + s2);
            ResultSet rs = ps.executeQuery();
            TeamBean team = null;
            while (rs.next()) {
                int countryID = rs.getInt("pk_teamid");
                if (team == null || team.getTeamID() != countryID) {
                    team = new TeamBean();
                    team.setTeamID(countryID);
                    team.setCountry(rs.getString("f_teamName"));
                    teamLst.add(team);
                }
                PlayerBean p = new PlayerBean();
                p.setPlayerID(rs.getInt("pk_playerid"));
                p.setName(rs.getString("f_name"));
                team.getMyPlayer().add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return teamLst;
    }

    //查询出在”巴黎“比赛的有哪些门将，要求也要显示他们所属的国家队
    public static ArrayList<TeamBean> getGoalkeeperWithCountry(String address, String place) {
        ArrayList<TeamBean> teamLst = new ArrayList<>();
        String sql = "select pk_teamid,f_teamName,pk_playerid,f_name from t_group,t_team,t_player where pk_groupid=fk_groupid and pk_teamid=fk_teamid " +
                "and f_groupAddress=? and f_place=? order by pk_teamid";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, address);
            ps.setString(2, place);
            ResultSet rs = ps.executeQuery();
            TeamBean team = null;
            while (rs.next()) {
                int countryID = rs.getInt("pk_teamid");
                if (team == null || team.getTeamID() != countryID) {
                    team = new TeamBean();
                    team.setTeamID(countryID);
                    team.setCountry(rs.getString("f_teamName"));
                    teamLst.add(team);
                }
                PlayerBean p = new PlayerBean();
                p.setPlayerID(rs.getInt("pk_playerid"));
                p.setName(rs.getString("f_name"));
                team.getMyPlayer().add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return teamLst;
    }

    //查询出“A组”各队带了哪些前锋
    public static ArrayList<TeamBean> getForwardWithPlayer(String group, String place) {
        ArrayList<TeamBean> teamLst = new ArrayList<>();
        String sql = "select pk_teamid,f_teamName,pk_playerid,f_name from t_group,t_team,t_player where pk_groupid=fk_groupid " +
                "and pk_teamid=fk_teamid and f_groupName=? and f_place=? order by pk_teamid";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, group);
            ps.setString(2, place);
            ResultSet rs = ps.executeQuery();
            TeamBean team = null;
            while (rs.next()) {
                int countryID = rs.getInt("pk_teamid");
                if (team == null || team.getTeamID() != countryID) {
                    team = new TeamBean();
                    team.setTeamID(countryID);
                    team.setCountry(rs.getString("f_teamName"));
                    teamLst.add(team);
                }
                PlayerBean p = new PlayerBean();
                p.setPlayerID(rs.getInt("pk_playerid"));
                p.setName(rs.getString("f_name"));
                team.getMyPlayer().add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return teamLst;
    }

    //在”F组“中增加”中国队“，同时让该小组的球队数目+1
    public static void insert(String group, String name) {
        String sql1 = "select pk_groupid from t_group where f_groupName=?";
        String sql2 = "insert into t_team(f_teamName,f_teamCountry,f_teamCoach,f_teamNum,fk_groupid) VALUES" +
                "(?,'中国','李霄鹏',23,?)";
        String sql3 = "update t_group set f_groupNum=f_groupNum+1 where f_groupName=?";
        try (Connection con = DBUtil.getConnection()) {
            con.setAutoCommit(false);
            try (PreparedStatement ps1 = con.prepareStatement(sql1);
                 PreparedStatement ps2 = con.prepareStatement(sql2);
                 PreparedStatement ps3 = con.prepareStatement(sql3)) {
                ps1.setString(1, group);
                ResultSet rs1 = ps1.executeQuery();
                while (rs1.next()) {
                    int id = rs1.getInt("pk_groupid");
                    //System.out.println(id);
                    ps2.setString(1, name);
                    ps2.setInt(2, id);
                    ps2.executeUpdate();

                    ps3.setString(1, group);
                    ps3.executeUpdate();
                }
                con.commit();
            } catch (SQLException e) {
                con.rollback();
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
